package www.lagou.app;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import www.lagou.utils.DruidUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @author wanggege
 */
public class Demo01Test {

    /**
     * 编程题1
     * 需求：按照步骤 实现卡号：1122334455向55443332211转账5000元的操作
     */
    @Test
    public void transfer() {
        //获取系统时间，设置日期格式
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = new Date();

        //使用连接池创建QueryRunner对象 手动模式
        QueryRunner qr = new QueryRunner();

        //编写SQL查询卡号为1122334455的余额
        String sql = "select balance from account where card = ?";
        Object[] param = {"1122334455"};

        //编写转出SQL
        String sql1 = "update account set balance = balance - ? where card = ?";
        //设置占位符
        Object[] param1 = {5000.0, "1122334455"};

        //编写转入SQL
        String sql2 = "update account set balance = balance + ? where card = ?";
        Object[] param2 = {5000.0, "55443332211"};

        //编写转出记录SQL
        String sql3 = "insert into TRANSACTION values(?,?,?,?,?)";
        Object[] param3 = {1, "1122334455", "转出", 5000.0, df.format(date)};

        //编写转入记录SQL
        String sql4 = "insert into TRANSACTION values(?,?,?,?,?)";
        Object[] param4 = {2, "55443332211", "转入", 5000.0, df.format(date)};

        Connection con = null;
        try {
            con = DruidUtils.dataSource.getConnection();

            //查询余额
            Object balance = qr.query(con, sql, new ScalarHandler<>(), param);
            //判断余额是否足够  转账金额 money
            double money = 5000.0;
            if ((Double) balance < money) {
                System.out.println("余额不足！");
                return;
            } else {
                //开启事务
                con.setAutoCommit(false);
                //进行转账操作
                qr.update(con, sql1, param1);
                qr.update(con, sql2, param2);
                qr.update(con, sql3, param3);
                qr.update(con, sql4, param4);

                //提交事务
                con.commit();
            }
            System.out.println("转账成功！");
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                con.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        } finally {
            DbUtils.closeQuietly(con);
        }


    }

}
